package DBside;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map.Entry;
import tables.Album;
import tables.AlbumVSSong;
import tables.ArtistVSSong;
import tables.Genre;
import tables.Artist;
import tables.Song;

import java.sql.ResultSetMetaData;

import DBside.Tools.Connector;
import DBside.Tools.Type;
import LogicSide.Session;

import entities.CD;
import entities.Track;

/**
 * @author shaibela
 * 
 *         types: Connection: A connection (session) with a specific database.
 *         SQL statements are executed and results are returned within the
 *         context of a connection.
 * 
 **         PreparedStatement: An object that represents a precompiled SQL
 *         statement. INSERT, CREATE, UPDATE, DELETE,... for SELECT, see
 *         ResultSet
 * 
 **         Statement: The object used for executing a static SQL statement and
 *         returning the results it produces.
 * 
 **         ResultSet A table of data representing a database result set, which
 *         is usually generated by executing a statement that queries the
 *         database.
 * 
 **         ResultSetMetaData An object that can be used to get information about
 *         the types and properties of the columns in a ResultSet object.
 * 
 */

public class JDBCConnection {
	private Connection connection;
	private PreparedStatement songPreparedStatement;
	private PreparedStatement albumPreparedStatement;
	private PreparedStatement albumVSSongPreparedStatement;
	private PreparedStatement genrePreparedStatement;
	private PreparedStatement artistPreparedStatement;
	private PreparedStatement artistVSSongPreparedStatement;

	private Boolean batch;

	// empty constructor
	public JDBCConnection() {
		this.connection = null;
		this.songPreparedStatement = null;
		this.albumPreparedStatement = null;
		this.albumVSSongPreparedStatement = null;
		this.genrePreparedStatement = null;
		this.artistPreparedStatement = null;
		this.artistVSSongPreparedStatement = null;

		this.batch = false;
	}

	public Connection getConnection() {
		return connection;
	}

	// ******* database communication function *******\\
	/**
	 * post: the connection (null on error)
	 * @throws Exception 
	 */
	public int openConnection(String url, String userName, String password) throws Exception {
		// *****STEP 1: LOAD DRIVER*****\\

		System.out.println("try loding JDBC driver...");
		// loading the driver
		try {
			// check if the the JDBC driver class exists
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			Tools.printError("load the MySQL JDBC driver", e);
			throw new Exception("fail to load the MySQL JDBC driver - check exsitence");
		}
		System.out.println("Driver loaded successfully!");
		// *****STEP 2: OPEN CONNECTION*****\\
		System.out.print("Trying to connect...");
		try {
			this.connection = DriverManager.getConnection(url, userName,
					password);
		} catch (SQLException e) {
			Tools.printError("create connection", e);
			throw new Exception("fail open connection");
		}
		System.out.println("Connected!");
		// *****STEP 3: build the PrepareStatemnet objects*****\\
		try {
			this.songPreparedStatement = this.connection
					.prepareStatement(prepareStatementInsert(Table.SONG));
			this.albumPreparedStatement = this.connection
					.prepareStatement(prepareStatementInsert(Table.ALBUM));
			this.albumVSSongPreparedStatement = this.connection
					.prepareStatement(prepareStatementInsert(Table.ALBUMVSSONG));
			this.genrePreparedStatement = this.connection
					.prepareStatement(prepareStatementInsert(Table.GENRE));
			this.artistPreparedStatement = this.connection
					.prepareStatement(prepareStatementInsert(Table.ARTIST));
			this.artistVSSongPreparedStatement = this.connection
					.prepareStatement(prepareStatementInsert(Table.ARTISTVSSONG));

		} catch (SQLException e) {
			Tools.printError("build the PrepareStatemnet objects", e);
			throw new Exception("fail to build preparestatment objects");
		}
		return 1;
	}

	

	/**
	 * close the connection
	 */
	public int closeConnection() {
		closePreparedStatement();
		System.out.println("try closing the connection...");
		// closing the connection
		try {
			connection.close();
		} catch (SQLException e) {
			System.out.println("Unable to close the connection. \n"
					+ "Exception:" + e.toString());
			return 0;
		}
		System.out.println("connection closed!");
		return 1;
	}

	// ******* SQL statements *******\\

	/*********************** create ************************************/

	/**
	 * create a new table notice: the use of preparestatment object is with a
	 * string only - there's no need to set different values
	 * 
	 * @param tableName
	 * @param attributesAndTypes
	 * @return
	 */
	public int create(Table table, ArrayList<TableItem> attributesAndTypesList) {
		String attributesAndTypes = attributesAndTypesList.get(0)
				.getAttribute()
				+ " "
				+ attributesAndTypesList.get(0).getType().toString();
		if (attributesAndTypesList.get(0).getValue() != null) {
			attributesAndTypes += "("
					+ attributesAndTypesList.get(0).getValue() + ")";
		}
		for (int i = 1; i < attributesAndTypesList.size(); i++) {
			attributesAndTypes += ","
					+ attributesAndTypesList.get(i).getAttribute() + " "
					+ attributesAndTypesList.get(i).getType().toString();
			if (attributesAndTypesList.get(i).getValue() != null) {
				attributesAndTypes += "("
						+ attributesAndTypesList.get(i).getValue() + ")";
			}
		}

		String sqlStatement = Action.CREATE.getActionCommand(table).replace(
				"!", attributesAndTypes);

		return execute(Action.CREATE.toString(), sqlStatement);
	}

	/*********************** end create ************************************/

	/*********************** insert ************************************/

	
	/**
	 * insert values to table notice: the use of prepareStatment object is with
	 * a string only - there's no need to set different values
	 * 
	 * @param tableName
	 * @param attributesANDValues
	 * @return ResultSet holding only the generated keys columns and the rows
	 *         that inserted, or null if fail
	 */
	public ResultSet insert(Table table, TableItem... attributesANDValues) {
		PreparedStatement prepareStatement = null;
		try {
			String sqlStatement = "INSERT INTO "
					+ prepareStatementInsert(table, attributesANDValues);

			// prepareStatement will return a prepared Statement with
			// Statement.RETURN_GENERATED_KEYS
			prepareStatement = prepareStatement(sqlStatement,
					attributesANDValues);

			if (prepareStatement.executeUpdate() == 0) {
				// then no insert made
				return null;
			} else {
				ResultSet generatedKeys = prepareStatement.getGeneratedKeys();
				return generatedKeys;
			}
		} catch (SQLException e) {
			Tools.printError(Action.INSERT.toString(), e);
			return null;
		}
	}

	/*********************** end insert ************************************/

	/*********************** insert Batch ************************************/

	public int executeBatch() {
		if (!batch) {
			return -1;
		}
		System.out.println("try to execute batch...");

		// *********STEP 1: execute batch on all tables**********\\
		// first the entity tables
		try {
			this.albumPreparedStatement.executeBatch();
		} catch (SQLException e) {
			Tools.printError("executeBatch: albumPreparedStatement"
					+ this.albumPreparedStatement, e);
		}
		try {
			this.genrePreparedStatement.executeBatch();
		} catch (SQLException e) {
			Tools.printError("executeBatch: genrePreparedStatement"
					+ this.genrePreparedStatement, e);
		}
		try {
			this.songPreparedStatement.executeBatch();
		} catch (SQLException e) {
			Tools.printError("executeBatch: songPreparedStatement"
					+ this.songPreparedStatement, e);
		}
		try {
			this.artistPreparedStatement.executeBatch();
		} catch (SQLException e) {
			Tools.printError("executeBatch: artistPreparedStatement"
					+ this.artistPreparedStatement, e);
		}
		// then the relationship tables
		try {
			this.artistVSSongPreparedStatement.executeBatch();
		} catch (SQLException e) {
			Tools.printError("executeBatch: artistVSSongPreparedStatement"
					+ this.artistVSSongPreparedStatement, e);
		}

		try {
			this.albumVSSongPreparedStatement.executeBatch();
		} catch (SQLException e) {
			Tools.printError("executeBatch: albumVSSongPreparedStatement"
					+ this.albumVSSongPreparedStatement, e);
		}

		// *********STEP 2: clear all PareparedStatment objects**********\\
		try {
			this.albumPreparedStatement.clearParameters();
			this.albumVSSongPreparedStatement.clearParameters();
			this.songPreparedStatement.clearParameters();
			this.artistPreparedStatement.clearParameters();
			this.artistVSSongPreparedStatement.clearParameters();
			this.genrePreparedStatement.clearParameters();
			// *********STEP 3: relax batch status **********\\
			this.connection.commit();
			System.out.println("success");
			this.batch = false;

			this.connection.setAutoCommit(true);
		} catch (SQLException e) {
			Tools.printError("executeBatch", e);
			return -1;
		}
		return 1;
	}

	public void addToBatch(CDBox box) {
		checkBatch();

		// fix all the ids of the entities found in the database and set their
		// inDB field to true
		box.updateIdsFromDataBase(
				Table.ALBUM,
				select("id, name", box.getAlbumsName(), Connector.OR, null,
						Table.ALBUM));
		box.updateIdsFromDataBase(
				Table.ARTIST,
				select("id, name", box.getArtistsName(), Connector.OR, null,
						Table.ARTIST));
		box.updateIdsFromDataBase(
				Table.GENRE,
				select("id, name", box.getGenresName(), Connector.OR, null,
						Table.GENRE));
		// notice: the call with SONG must be done after the call with ARTIST
		// and GENRE
		box.updateIdsFromDataBase(
				Table.SONG,
				select("id, name, genre", box.getSongsName(), Connector.OR,
						null, Table.SONG));

		int albumId;
		int genreId;
		int artistId;
		int songId;

		Artist artist;

		for (CD cd : box.getCds()) {

			albumId = box.fixEntityId(cd.getAlbum(), Table.ALBUM);
			addToBatch(cd.getAlbum());

			genreId = box.fixEntityId(cd.getGenre(), Table.GENRE);
			addToBatch(cd.getGenre());

			// fix the artist and the song
			for (Entry<String, ArrayList<Track>> entity : cd.getTracksMap()
					.entrySet()) {
				// get the artist who represent this entry and insert only it.
				artist = entity.getValue().get(0).getArtist();
				artistId = box.fixEntityId(artist, Table.ARTIST);
				addToBatch(artist);

				// inserting all of artist songs:
				for (Track track : entity.getValue()) {
					// fix this song genre:
					track.getSong().setGenreId(genreId);
					// continue with batching
					songId = box.fixEntityId(track.getSong(), Table.SONG);
					if (addToBatch(track.getSong()) != -1) {
						// then a new song has been inserted
						addToBatch(new ArtistVSSong(artistId, songId));
						addToBatch(new AlbumVSSong(albumId, songId));
					}
				}
			}
		}
	}

	private int addToBatch(Genre genre) {
		if (genre.isInDB()) {
			return genre.getId();
		} else {
			// genre.fixId();
			try {
				this.genrePreparedStatement.setInt(Tools.getIdColumnNumber(),
						genre.getId());
				this.genrePreparedStatement.setString(
						Tools.getNameColumnNumber(), genre.getName());
				this.genrePreparedStatement.setInt(3, genre.getParentGenreId());
				this.genrePreparedStatement.addBatch();

				return genre.getId();
			} catch (SQLException e) {
				Tools.printError("addToBatch: Genre", e);
				return -1;
			}
		}
	}

	private int addToBatch(Album album) {
		if (album.isInDB()) {
			return album.getId();
		} else {
			// album.fixId(); //done already in fixEntityId
			try {
				this.albumPreparedStatement.setInt(Tools.getIdColumnNumber(),
						album.getId());
				this.albumPreparedStatement.setString(
						Tools.getNameColumnNumber(), album.getName());
				this.albumPreparedStatement.setInt(3, album.getYear());
				this.albumPreparedStatement.setString(4,
						album.getFreeDBIdToString());
				this.albumPreparedStatement.addBatch();

				return album.getId();
			} catch (SQLException e) {
				Tools.printError("addToBatch: album - id = " + album.getId()
						+ ", name = " + album.getName(), e);
				return -1;
			}
		}
	}

	private int addToBatch(Artist artist) {
		// no need in doing artist.fixId() since it's done already by
		// addBatch(BoxCD)
		if (artist.isInDB()) {
			return artist.getId();
		} else {
			try {
				this.artistPreparedStatement.setInt(Tools.getIdColumnNumber(),
						artist.getId());
				this.artistPreparedStatement.setString(
						Tools.getNameColumnNumber(), artist.getName());
				this.artistPreparedStatement.addBatch();
				return artist.getId();
			} catch (SQLException e) {
				Tools.printError("addToBatch: Artist", e);
				return -1;
			}
		}
	}

	// this function is different then the other functions
	private int addToBatch(Song song) {
		// song.fixId();
		if (song.isInDB()) {
			// if no insert done - return -1
			return -1;
		} else {
			try {
				this.songPreparedStatement.setInt(Tools.getIdColumnNumber(),
						song.getId());
				this.songPreparedStatement.setString(
						Tools.getNameColumnNumber(), song.getName());
				this.songPreparedStatement.setInt(3, song.getGenreId());
				this.songPreparedStatement.addBatch();

				return song.getId();
			} catch (SQLException e) {
				Tools.printError("addToBatch: Song", e);
				return -1;
			}

		}
	}

	private Boolean addToBatch(ArtistVSSong artistVSSong) {
		try {
			this.artistVSSongPreparedStatement.setInt(1, artistVSSong.getIdA());
			this.artistVSSongPreparedStatement.setInt(2, artistVSSong.getIdB());
			this.artistVSSongPreparedStatement.addBatch();
			return true;
		} catch (SQLException e) {
			Tools.printError("addToBatch: ArtistVSSong", e);
			return false;
		}
	}

	private Boolean addToBatch(AlbumVSSong albumVSSong) {
		try {
			this.albumVSSongPreparedStatement.setInt(1, albumVSSong.getIdA());
			this.albumVSSongPreparedStatement.setInt(2, albumVSSong.getIdB());
			this.albumVSSongPreparedStatement.addBatch();
			return true;
		} catch (SQLException e) {
			Tools.printError("addToBatch: AlbumVSSong", e);
			return false;
		}
	}



	/*********************** end insert Batch ************************************/

	/*********************** start delete	 ****************************************\


	

	/*
	 * result = DELETE FROM deleteTables WHERE whereAttributes IN (SELECT
	 * selectAttributes FROM selectTables WHERE ( conditions[0].getArrribute() =
	 * conditions[0].getValue() connector conditions[1].getArrribute() =
	 * conditions[1].getValue() ...))
	 */
	public boolean deleteFromSelect(Table[] deleteTables,
			String[] whereAttributes, String[] selectAttributes,
			Table[] selectTables, TableItem[] conditions, Connector connector) {
		String sqlStatement = "DELETE FROM "
				+ fromArrayToSQLString(deleteTables) + " WHERE "
				+ fromArrayToSQLString(whereAttributes) + " IN " + "(SELECT "
				+ fromArrayToSQLString(selectAttributes) + " FROM "
				+ fromArrayToSQLString(selectTables);

		if (conditions != null && conditions.length > 0) {
			sqlStatement += " WHERE ";
			sqlStatement += prepareSQLStatementConditions(conditions, connector);
		}
		sqlStatement += ")";

		PreparedStatement preparedStatement = prepareStatement(sqlStatement,
				conditions);
		try {
			Boolean result = preparedStatement.execute();
			return result;
		} catch (SQLException e) {
			Tools.printError(Action.DELETE.toString() + preparedStatement, e);
		}finally{//TODO:
			close(null, preparedStatement);
		}
		return false;
		
	}

	// result = DELETE FROM tables WHERE (conditions[0].getArrribute() =
	// conditions[0].getValue() connector conditions[1].getArrribute() =
	// conditions[1].getValue() ...)
	public boolean delete(TableItem[] conditions, Connector connector,
			Table... tables) {
		// prepare sqlStatement
		String sqlStatement = "DELETE FROM " + fromArrayToSQLString(tables);

		if (conditions != null && conditions.length > 0) {
			sqlStatement += " WHERE ";
			sqlStatement += prepareSQLStatementConditions(conditions, connector);
		}

		PreparedStatement preparedStatement = prepareStatement(sqlStatement,
				conditions);
		try {
			Boolean result = preparedStatement.execute();
			return result;
		} catch (SQLException e) {
			Tools.printError(
					Action.DELETE.toString() + " " + preparedStatement, e);
		} catch (Exception e) {
			Tools.printError(
					Action.DELETE.toString() + " " + preparedStatement, e);
		}finally{//TODO:
			close(null, preparedStatement);
		}
		return false;

	}

	public void deleteAll() {
		// for(Table t:Table.values())

		// delete(null, null, t);
		delete(null, null, Table.ALBUMVSSONG);
		delete(null, null, Table.ARTISTVSSONG);
		delete(null, null, Table.PLAYLISTVSSONG);
		delete(null, null, Table.FOLLOWERSRELATIONSHIP);
		delete(null, null, Table.RECOMMENDERS);
		delete(null, null, Table.USERVSPLAYLIST);
		for (Table t : Table.values())

			delete(null, null, t);
	}

	// ************************end of delete ***************************************\\
	
	// ******************** begin select ****************************\\



	// result = SELECT attributes FROM tables
	public ResultSet select(String attributes, Table... tables) {
		return select(attributes, new TableItem[0], null, null, tables);
	}

	// result = SELECT attributes FROM tables WHERE name = consitionaOnName[0]
	// connector name = consitionaOnName[1] ...
	public ResultSet select(String attributes, String[] conditionsOnName,
			Connector connector, String orderBy, Table... tables) {
		TableItem[] tableItems = new TableItem[conditionsOnName.length];
		for (int i = 0; i < tableItems.length; i++) {
			tableItems[i] = new TableItem("name", Tools.Type.VARCHAR,
					conditionsOnName[i]);
		}
		return select(attributes, tableItems, connector, orderBy, tables);
	}

	// result = SELECT attributes FROM tables WHERE
	// (conditions[0].getArrribute() = conditions[0].getValue() connector
	// conditions[1].getArrribute() = conditions[1].getValue() ...)
	public ResultSet select(String attributes, TableItem[] conditions,
			Connector connector, String orderBy, Table... tables) {

		return select(attributes, conditions, connector, null, null, null,
				orderBy, tables);
	}

	/*
	 * result = SELECT attributes FROM tables WHERE
	 * (conditionsA[0].getArrribute() = conditionsA[0].getValue() connectorA
	 * conditionsA[1].getArrribute() = conditionsA[1].getValue() ...)
	 * connectorAB (conditionsB[0].getArrribute() = conditionsB[0].getValue()
	 * connectorB conditionsB[1].getArrribute() = conditionsB[1].getValue() ...)
	 */
	public ResultSet select(String attributes, TableItem[] conditionsA,
			Connector connectorA, Connector connectorAB,
			TableItem[] conditionsB, Connector connectorB, String orderBy,
			Table... tables) {

		// this is instead of the commented sector below
		String sqlStatement = "SELECT " + attributes + " FROM ";
		sqlStatement += fromArrayToSQLString(tables);

		if (conditionsA != null && conditionsA.length > 0) {
			sqlStatement += " WHERE ";
			if (conditionsA.length > 0
					&& conditionsA[0].getType() == Type.SEARCH)// TODO
			{
				String attributes2 = "Song.id as Id, Song.name as Title, Genre.name as Genre, Artist.name as Artist, Album.name as Album, Album.year as Year";
				sqlStatement = "SELECT " + attributes2 + " FROM ";
				sqlStatement += fromArrayToSQLString(tables);
				sqlStatement += " WHERE ";
				sqlStatement += "(AlbumVSSong.album_id = Album.id AND AlbumVSSong.song_id = ArtistVSSong.song_id AND ArtistVSSong.artist_id = Artist.id AND Song.id = ArtistVSSong.song_id AND Song.genre = Genre.id AND ";
				for (TableItem t : conditionsA)
					t = new TableItem(t.getAttribute(), t.getType(), "\\%"
							+ t.getValue() + "\\%");
				sqlStatement += prepareSQLStatementConditionsSearch(
						conditionsA, connectorA).substring(1)
						+ " LIMIT 0,100";
			} else
				sqlStatement += prepareSQLStatementConditions(conditionsA,
						connectorA);
		}

		if (conditionsB != null && conditionsB.length > 0) {
			sqlStatement += " " + connectorAB.getName() + " "
					+ prepareSQLStatementConditions(conditionsB, connectorB);
		}

		if (orderBy != null && orderBy.length() > 0) {
			sqlStatement += " ORDER BY " + orderBy;
		}
		PreparedStatement preparedStatement = prepareStatement(sqlStatement,
			conditionsA, conditionsB);
		
		ResultSet resultSet = null;
		try {
			resultSet = preparedStatement.executeQuery();
			return resultSet;
		} catch (SQLException e) {
			Tools.printError(Action.SELECT.toString(), e);
		}
		return null;

	}

	// result = SELECT id FROM tables WHERE name = consitionaOnName
	public int selectById(String conditionsOnName, Table... tables) {
		TableItem[] tableItems = { new TableItem("name", Tools.Type.VARCHAR,
				conditionsOnName) };
		ResultSet resultSet = select("id", tableItems, null, null, tables);
		try {
			if (resultSet.next())
				return resultSet.getInt("id");
		} catch (SQLException e) {
			Tools.printError("selectById", e);
		}
		return -1;
	}

	// result = SELECT name FROM tables WHERE id = consitionaOnId
	public String selectByName(int consitionaOnId, Table... tables) {
		TableItem[] tableItems = { new TableItem("id", Tools.Type.INT,
				consitionaOnId) };
		ResultSet resultSet = select("name", tableItems, null, null, tables);
		try {
			if (resultSet.next())
				return resultSet.getString("name");
		} catch (SQLException e) {
			Tools.printError("selectByName", e);
		}
		return null;
	}
	//****************** end of select ****************************\\
	
	// ******* tools *******\\
	public int execute(String action, String sqlStatement) {
		PreparedStatement preparedStatement = null;
		try {
			// build a new PreparedStatement object of type INSERT
			preparedStatement = this.connection
					.prepareStatement(sqlStatement);
			// execute!
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			Tools.printError(action, e);
			return 0;
		}finally{//TODO
			close(null, preparedStatement);
		}
		return 1;
	}



	// ******************************* specific tools *******************************\\
	
	private PreparedStatement prepareStatement(String sqlStatement,
			TableItem[] conditions) {
		return prepareStatement(sqlStatement, conditions, null);
	}

	/**
	 * mission: replace every question mark with the proper value and return a
	 * prepared statement sqlStatement = (conditions[0].getArrribute() = ?
	 * connector conditions[1].getArrribute() = ? ...) result =
	 * (conditions[0].getArrribute() = conditions[0].getValue() connector
	 * conditions[1].getArrribute() = conditions[1].getValue() ...)
	 */
	private PreparedStatement prepareStatement(String sqlStatement,
			TableItem[] conditionsA, TableItem[] conditionsB) {
		try {
			PreparedStatement preparedStatement = null;
			preparedStatement = this.connection.prepareStatement(sqlStatement,
					Statement.RETURN_GENERATED_KEYS);

			if (conditionsA == null) {
				return preparedStatement;
			}
			// fix the question marks of conditionsA
			for (int i = 0; i < conditionsA.length; i++) {
				if (conditionsA[i].getType().is(Tools.Type.VARCHAR)) {
					preparedStatement.setString(i + 1,
							(String) conditionsA[i].getValue());
				} else if (conditionsA[i].getType().is(Tools.Type.INT)) {
					preparedStatement.setInt(i + 1,
							(Integer) conditionsA[i].getValue());
				} else if (conditionsA[i].getType().is(Tools.Type.SEARCH)) {
					preparedStatement.setString(i + 1, (String) "%"
							+ conditionsA[i].getValue() + "%");
				} else if (conditionsA[i].getType().is(Tools.Type.DATE)) {
					preparedStatement.setDate(i + 1,
							(Date) conditionsA[i].getValue());
				} else if (conditionsA[i].getType().is(Tools.Type.DOUBLE)) {
					preparedStatement.setDouble(i + 1,
							(Double) conditionsA[i].getValue());
				} else {
					// not supported...
					Tools.printError(
							"prepareStatementCondition: not supported type",
							null);
					return null;
				}
			}

			// check if finish
			if (conditionsB == null) {
				return preparedStatement;
			}

			// fix the question marks of conditionsB using offset
			int offset = conditionsA.length;
			for (int i = 0; i < conditionsB.length; i++) {
				if (conditionsB[i].getType().is(Tools.Type.VARCHAR)) {
					preparedStatement.setString(i + 1 + offset,
							(String) conditionsB[i].getValue());
				} else if (conditionsB[i].getType().is(Tools.Type.INT)) {
					preparedStatement.setInt(i + 1 + offset,
							(Integer) conditionsB[i].getValue());
				} else {
					// not supported...
					Tools.printError(
							"prepareStatementSelect: not supported type", null);
					return null;
				}
			}
			return preparedStatement;
		} catch (SQLException e) {
			Tools.printError("prepareStatementConditions", e);
			return null;
		} catch (Exception e) {
			Tools.printError("prepareStatementConditions. sqlStatement = "
					+ sqlStatement, e);
			return null;
		}
	}

	// return = (conditions[0].getArrribute() = ? connector
	// conditions[1].getArrribute() = ? ...)
	private String prepareSQLStatementConditions(TableItem[] conditions,
			Connector connector) {
		String sqlStatement = "(";

		sqlStatement += conditions[0].getAttribute() + " = ?";
		for (int i = 1; i < conditions.length; i++) {
			sqlStatement += " " + connector.getName() + " "
					+ conditions[i].getAttribute() + " = ?";
		}

		return sqlStatement + ")";
	}

	private String prepareSQLStatementConditionsSearch(TableItem[] conditions,
			Connector connector) {
		String sqlStatement = "(";

		sqlStatement += conditions[0].getAttribute() + " like ?";
		for (int i = 1; i < conditions.length; i++) {
			sqlStatement += " " + connector.getName() + " "
					+ conditions[i].getAttribute() + " like ?";
		}

		return sqlStatement + ")";
	}
	
	/**
	 * being used by openConnection
	 * 
	 * @param table
	 *            pre: table must have column named "id"
	 * @return
	 */
	private int getLastId(Table table) {
		ResultSet resultSet = null;
		try {
			resultSet = this.connection.prepareStatement(
					"SELECT id FROM " + table.getName()
							+ " ORDER BY id DESC LIMIT 0,1").executeQuery();

			if (Tools.isEmpty(resultSet)) {
				return 0;
			} else {

				resultSet.next();
				return resultSet.getInt(1);

			}
		} catch (SQLException e) {
			Tools.printError("getLastId on " + table.getName(), e);
			return 0;
		}//TODO: 
		finally {
			close(resultSet, null);
		}
	}

	public static void close(ResultSet resultSet, PreparedStatement preparedStatement) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				Tools.printError("The result set cannot be closed.", e);
			}
		}
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				Tools.printError("The statement cannot be closed.", e);
			}
		}
	}

	private String prepareStatementInsert(Table table) {
		String result = "INSERT INTO " + table.getName() + " (";
		String[] attributes = getTableAttributes(table);
		String questionMarks = "";
		for (int i = 0; i < attributes.length - 1; i++) {
			// id increments Automatically so we don't update it
			if (attributes[i] != "id") {
				result += attributes[i] + ",";
				questionMarks += "?,";
			}
		}
		result += attributes[attributes.length - 1];
		questionMarks += "?";
		result += ") VALUES(" + questionMarks + ")";
		return result;
	}

	public int closePreparedStatement() {
		System.out.println("try closing preparedStatement...");
		try {
			this.albumPreparedStatement.close();
			this.albumVSSongPreparedStatement.close();
			this.artistPreparedStatement.close();
			this.artistVSSongPreparedStatement.close();
			this.genrePreparedStatement.close();
			this.songPreparedStatement.close();
		} catch (SQLException e) {
			Tools.printError("close preparedStatement", e);
			return 0;
		}
		System.out.println("preparedStatement closed!");
		return 1;
	}
	
	public ResultSet getEmptyTable(Table table) {
		try {
			return this.connection.prepareStatement(
					"SELECT * FROM " + table.getName() + " LIMIT 0,0")
					.executeQuery();
		} catch (SQLException e) {
			Tools.printError("getEmptyTable", e);
			return null;
		}
	}
	
	public int getTableAttributesNumber(Table table) {
		try {
			return getEmptyTable(table).getMetaData().getColumnCount();
		} catch (SQLException e) {
			Tools.printError("getTableAttributesNumber", e);
			return -1;
		}
	}

	/**
	 * return an array of table attributes with the attribute "id" notice the
	 * commented part in the function: if you would like to return the
	 * attributes accept for the "id", you can uncomment it
	 * 
	 * @param table
	 * @return
	 */
	public String[] getTableAttributes(Table table) {
		try {
			ResultSetMetaData resultSetMetaData = select("*", table)
					.getMetaData();
			int resultSize = resultSetMetaData.getColumnCount();
			int begin = 1;
			String[] result = new String[resultSize];
			for (int i = 0; i < resultSize; i++) {
				result[i] = resultSetMetaData.getColumnName(begin + i);
			}
			return result;
		} catch (SQLException e) {
			Tools.printError("get Table Attributes", e);
			return null;
		}
	}

	/*
	 * if objectArray != null and objectArray length > 0 then: result =
	 * objectArray[0],objectArray[1],objectArray[2],... else result = *
	 */
	private String fromArrayToSQLString(Object[] objectArray) {

		if (objectArray == null || objectArray.length == 0) {
			return "*";
		} else {
			String result = objectArray[0].toString();
			for (int i = 1; i < objectArray.length; i++) {
				result += "," + objectArray[i].toString();
			}
			return result;
		}
	}
	
	private void checkBatch() {
		if (!this.batch) {
			this.batch = true;
			try {
				this.connection.setAutoCommit(false);
			} catch (SQLException e) {
				Tools.printError("checkBatch", e);
			}
		}

		tryToInitalizeVirtualIds();
	}

	private void tryToInitalizeVirtualIds() {
		if (!tables.Album.isInitalized()) {
			tables.Album.initalizeVirtualId(getLastId(Table.ALBUM));
		}
		if (!tables.Artist.isInitalized()) {
			tables.Artist.initalizeVirtualId(getLastId(Table.ARTIST));
		}
		if (!tables.Genre.isInitalized()) {
			tables.Genre.initalizeVirtualId(getLastId(Table.GENRE));
		}
		if (!tables.Song.isInitalized()) {
			tables.Song.initalizeVirtualId(getLastId(Table.SONG));
		}
		if (!tables.User.isInitalized()) {
			tables.User.initalizeVirtualId(getLastId(Table.USER));
		}
	}
	
	private String prepareStatementInsert(Table table,
			TableItem[] attributesANDValues) {
		String result = table.getName() + " (";
		String questionMarks = "";
		for (int i = 0; i < attributesANDValues.length - 1; i++) {
			result += attributesANDValues[i].getAttribute() + ",";
			questionMarks += "?,";
		}
		result += attributesANDValues[attributesANDValues.length - 1]
				.getAttribute();
		questionMarks += "?";
		result += ") VALUES(" + questionMarks + ")";
		return result;
	}

	
	// ******* ENUM *******\\
	/**
	 * ! => need to replace with unknown number of "?". $ => need to be replace
	 * with unknown number of attributes names.
	 * 
	 */
	public enum Action {
		CREATE("CREATE TABLE", "(!);"), INSERT("INSERT INTO", "($) VALUES(!)"), DELETE(
				"DELETE FROM", "WHERE !"), SELECT("SELECT $ FROM", "");

		private String actionCommand;
		private String remainder;

		private Action(String actionComannd, String remainder) {
			this.actionCommand = actionComannd;
			this.remainder = remainder;
		}

		public String getActionCommand(Table... tables) {
			String result = this.actionCommand + " ";
			for (int i = 0; i < tables.length - 1; i++) {
				result += tables[i].getName() + ",";
			}
			result += tables[tables.length - 1].getName();
			// add "(?,...,?)" in the size of number of attributes
			result += " " + getRemainder();
			return result;
		}

		public String getRemainder() {
			return remainder;
		}

	}

	public enum Genres {
		BLUES("blues", 1), CLASSICAL("classical", 2), COUNTRY("country", 3), DATA(
				"data", 4), FOLK("folk", 5), JAZZ("jazz", 6), NEWAGE("newage",
				7), REGGAE("reggae", 8), ROCK("rock", 9), SOUNDTRACK(
				"soundtrack", 10), MISC("misc", 11), NOTSUPPORTED("unkwon", 0);

		private final String name;
		private final int id;

		private Genres(String name, int id) {
			this.name = name;
			this.id = id;
		}

		public String getName() {
			return name;
		}

		public int getId() {
			return id;
		}

		public Boolean is(Genres genre) {
			return this.equals(genre);
		}

		public static Genres getGenreFromId(int id) {
			for (Genres genre : Genres.values()) {
				if (genre.getId() == id) {
					return genre;
				}
			}
			return null;
		}

		@Override
		public String toString() {
			return this.getName();
		}
	}

	/**
	 * the virtualId field is set to -1 if this table is a connection of many to
	 * many, in that case, the function getLastId won't try to find the last id
	 * (it's irrelevant).
	 * 
	 * @author hershalle
	 * 
	 */
	public enum Table {
		// entity tables:
		ALBUM("Album"), USER("User"), SONG("Song"), PLAYLIST("Playlist"), GENRE(
				"Genre"), ARTIST("Artist"), RECOMMENDERS("Recommenders"),
		// relationship tables
		ARTISTVSSONG("ArtistVSSong"), USERVSSONG("UserVSSong"), ALBUMVSSONG(
				"AlbumVSSong"), PLAYLISTVSSONG("PlaylistVSSong"), USERVSPLAYLIST(
				"UserVSPlaylist"), FOLLOWERSRELATIONSHIP(
				"FollowersRelationship"), USERHESTORY("UserHistory");
		private String name;

		private Table(String name) {
			this.name = name;
		}

		public String getName() {
			return this.name;
		}

		@Override
		public String toString() {
			return this.getName();
		}

	}
}